<?php

class FinanceController extends Controller
{
	
	/* Render Report */
	
	public function actionListnota()
	{

		$this->render('listnota',array());
	}
	
	public function actionShowallnota()
	{
		$sql = " select to_char(a.createdtime,'dd-mm-yyyy') as tgl_input
,a.nota_debet_no as no_nota
,a.premium as premi
,b.policy_no 
,a.discount as diskon_agra
,a.diskon_asuransi
,a.brokerage
,a.brokerage_percent
,a.nett_to_insurance
,a.nominal
,c.name as marketing
from 
ndk_health a
inner join reg_health_policy b on a.reg_health_policy_id = b.id
inner join mst_marketing c on b.mst_marketing_id = c.id
 ";
		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryAll();
		echo json_encode($datas);
		Yii::app()->end();
	}
	
	public function actionNeraca()
	{

		$this->render('neraca',array());
	}
	
	public function actionShowneraca()
	{
		$sql_investasi = " 
		select @ sum(debet) - sum(kredit) nilai from (
		select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A100%'  and a.nominal < 0
  )xx order by nota_note	 
)xx  ";
		$cmd_investasi = Yii::app()->db->createCommand($sql_investasi);
		$data_investasi = $cmd_investasi->queryRow();
		
		$piutang_premi = 0;
		$sql = " 
 select @ sum(debet) - sum(kredit) nilai from (
select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A300%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A300%'  and a.nominal < 0
  
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' 
		union
select
(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty
 -a.discount - a.diskon_asuransi) * coalesce(c.nominal) debet,0 kredit,a.nota_debet_no as nota_note

,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from
ndk_health_non a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from ndk_health_non_kredit a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
where posting = '2'
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '2'  
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' 
		union
	select
	(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty
 -a.discount - a.diskon_asuransi) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note

	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from
	ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
	inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
			where posting = '2' 
	union
	select 
	0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2' 
	union
	select coalesce(a.nominal,0) as debet,0 as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan

	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
	inner join reg_health_policy_non b on a.reg_policy_id = b.id
	where a.type_reg_policy = '2'  )xx order by nota_note	
)xx ";

		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryRow();
		
		
		
		$sql_hutangpremi = " select @ sum(kredit) - sum(debet) nilai from (
select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10H100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H100%'  and a.nominal < 0
  
union
select 
		 coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note
,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note

		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 

union
select 
		((a.premium + a.tpl - a.diskon_asuransi + a.policy_cost + a.stampduty_asuransi - a.brokerage - a.ppn
 + a.pph) * coalesce(c.nominal) ) + (coalesce(a.tpl,0) * coalesce(c.nominal) )
			as debet
		,0  as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
	0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note

	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'  
union
	select 0 as debet,coalesce(a.nominal,0) as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan

	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
  )xx order by nota_note	 		
		)xx	 ";
		$cmd_hutangpremi = Yii::app()->db->createCommand($sql_hutangpremi);
		$data_hutangpremi = $cmd_hutangpremi->queryRow();
		
		$sql_piutangjasakeperantaraan = " select @  sum(debet) - sum(kredit) nilai from (
select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A301%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A301%'  and a.nominal < 0
  )xx order by nota_note	 	
)xx ";
		$cmd_piutangjasakeperantaraan = Yii::app()->db->createCommand($sql_piutangjasakeperantaraan);
		$data_piutangjasakeperantaraan = $cmd_piutangjasakeperantaraan->queryRow();
		
		$sql_biayabayardimuka = " select @ sum(debet) - sum(kredit) nilai from (
		select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A400%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A400%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayabayardimuka = Yii::app()->db->createCommand($sql_biayabayardimuka);
		$data_biayabayardimuka = $cmd_biayabayardimuka->queryRow();
		
		$sql_piutanghasilinvestasi = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
 select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A500%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A500%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_piutanghasilinvestasi = Yii::app()->db->createCommand($sql_piutanghasilinvestasi);
		$data_piutanghasilinvestasi = $cmd_piutanghasilinvestasi->queryRow();
		
		$sql_piutanglainlain = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A700%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A700%'  and a.nominal < 0
  )xx order by nota_note	
)xx ";
		$cmd_piutanglainlain = Yii::app()->db->createCommand($sql_piutanglainlain);
		$data_piutanglainlain = $cmd_piutanglainlain->queryRow();
		
		$sql_aktivatetap = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A900%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A900%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_aktivatetap = Yii::app()->db->createCommand($sql_aktivatetap);
		$data_aktivatetap = $cmd_aktivatetap->queryRow();
		
		$sql_akumulasipenyusutan = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A901%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A901%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_akumulasipenyusutan = Yii::app()->db->createCommand($sql_akumulasipenyusutan);
		$data_akumulasipenyusutan = $cmd_akumulasipenyusutan->queryRow();
		
		$sql_hutangkomisi = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10H200%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H200%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_hutangkomisi = Yii::app()->db->createCommand($sql_hutangkomisi);
		$data_hutangkomisi = $cmd_hutangkomisi->queryRow();
		
		$sql_biayayangmasihharusdibayar = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10H400%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H400%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayayangmasihharusdibayar = Yii::app()->db->createCommand($sql_biayayangmasihharusdibayar);
		$data_biayayangmasihharusdibayar = $cmd_biayayangmasihharusdibayar->queryRow();
		
		$sql_hutanglainlain = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10H500%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H500%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_hutanglainlain = Yii::app()->db->createCommand($sql_hutanglainlain);
		$data_hutanglainlain = $cmd_hutanglainlain->queryRow();
		
		$sql_modaldisetor = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M100%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_modaldisetor = Yii::app()->db->createCommand($sql_modaldisetor);
		$data_modaldisetor = $cmd_modaldisetor->queryRow();
		
		$sql_agiodisagiosaham = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M200%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M200%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_agiodisagiosaham = Yii::app()->db->createCommand($sql_agiodisagiosaham);
		$data_agiodisagiosaham = $cmd_agiodisagiosaham->queryRow();
		
		$sql_cadangan = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M300%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M300%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_cadangan = Yii::app()->db->createCommand($sql_cadangan);
		$data_cadangan = $cmd_cadangan->queryRow();
		
		$sql_kenaikanpenurunansuratberharga = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	 select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M400%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M400%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_kenaikanpenurunansuratberharga = Yii::app()->db->createCommand($sql_kenaikanpenurunansuratberharga);
		$data_kenaikanpenurunansuratberharga = $cmd_kenaikanpenurunansuratberharga->queryRow();
		
		$sql_selisihpenilaianaktivatetap = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M500%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M500%'  and a.nominal < 0
  )xx order by nota_note	
)xx ";
		$cmd_selisihpenilaianaktivatetap = Yii::app()->db->createCommand($sql_selisihpenilaianaktivatetap);
		$data_selisihpenilaianaktivatetap = $cmd_selisihpenilaianaktivatetap->queryRow();
		
		$sql_labarugiditahan = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M600%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M600%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_labarugiditahan = Yii::app()->db->createCommand($sql_labarugiditahan);
		$data_labarugiditahan = $cmd_labarugiditahan->queryRow();
		
		$sql_labarugitahunberjalan = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10M700%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10M700%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_labarugitahunberjalan = Yii::app()->db->createCommand($sql_labarugitahunberjalan);
		$data_labarugitahunberjalan = $cmd_labarugitahunberjalan->queryRow();
		
		$sql_kasdanbank = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select case when nota_note like 'RC%' then debet - kredit
	else 0
	end as debet
	,case when nota_note like 'VC%' then kredit - debet
	else 0
	end as kredit
	,nota_note
	,keterangan 
	,tgl_transaksi
	from (
select sum(@ xx.debet) as debet,sum( @ xx.kredit) as kredit,nota_note as nota_note,'-' as keterangan
,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan


,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code    LIKE '10A200%' 
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'  AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'  AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   and a.nominal > 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   and a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   and a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan

,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code    LIKE '10A200%'   and a.nominal < 0
  )xx group by nota_note,tgl_transaksi
 )zz  
)xx ";
		$cmd_kasdanbank = Yii::app()->db->createCommand($sql_kasdanbank);
		$data_kasdanbank = $cmd_kasdanbank->queryRow();
		
		$sql_hutangpajak = " select @  coalesce(sum(kredit),0) - coalesce(sum(debet),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10H300%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10H300%'  and a.nominal < 0
  union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal)  as debet,0 as kredit,'PPn-'||a.nota_debet_no as nota_note
,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal) as debet,0 kredit,'PPn-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) as kredit,'PPn-'||a.nota_debet_no as nota_note,a
.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) kredit,'PPn-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'		
		 )xx order by nota_note	 
)xx  ";
		$cmd_hutangpajak = Yii::app()->db->createCommand($sql_hutangpajak);
		$data_hutangpajak = $cmd_hutangpajak->queryRow();
		
		$sql_piutangpajak = " 
select @  coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10A600%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10A600%'  and a.nominal < 0
  
		union
		select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note
 as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
  )xx order by nota_note	 
)xx";
		$cmd_piutangpajak = Yii::app()->db->createCommand($sql_piutangpajak);
		$data_piutangpajak = $cmd_piutangpajak->queryRow();
		
		
		
		$total_aktiva = $data_investasi['nilai'] + $data_kasdanbank['nilai'] + $datas['nilai'] + $data_piutangjasakeperantaraan['nilai'] + $data_biayabayardimuka['nilai'] + $data_piutanghasilinvestasi['nilai'] + $data_piutangpajak['nilai'] + $data_piutanglainlain['nilai'] + $data_aktivatetap['nilai'] + $data_akumulasipenyusutan['nilai'] ;
		
		$total_kewajiban = $data_hutangpremi['nilai'] + $data_hutangkomisi['nilai'] + $data_hutangpajak['nilai'] + $data_biayayangmasihharusdibayar['nilai'] + $data_hutanglainlain['nilai'] ;
		
		//$total = ($datas['nilai'] + $data_kasdanbank['nilai'] + $data_piutangpajak['nilai'] ) - ($data_hutangpremi['nilai'] + $data_hutangpajak['nilai']);
		
		$total = $total_aktiva - $total_kewajiban;

		$total_modalsendiri = $data_modaldisetor['nilai'] + $data_agiodisagiosaham['nilai'] + $data_cadangan['nilai'] + $data_kenaikanpenurunansuratberharga['nilai'] + $data_selisihpenilaianaktivatetap['nilai'] + $data_labarugiditahan['nilai'] + $total;
		
		$pasiva = $total_kewajiban + $total_modalsendiri;
		
		if(isset($_POST['yt0'])){
			$data = json_decode(json_encode(array("piutang_premi"=>" ".number_format($datas['nilai']),"hutang_premi"=>" ".number_format($data_hutangpremi['nilai']),"piutangjasakeperantaraan"=>" ".number_format($data_piutangjasakeperantaraan['nilai']),"kasdanbank"=>" ".number_format($data_kasdanbank['nilai']),"hutangpajak"=>" ".number_format($data_hutangpajak['nilai']),"piutangpajak"=>" ".number_format($data_piutangpajak['nilai']),"investasi"=>" ".number_format($data_investasi['nilai']),"biayabayardimuka"=>" ".number_format($data_biayabayardimuka['nilai']),"piutanghasilinvestasi"=>" ".number_format($data_piutanghasilinvestasi['nilai']),"piutanglainlain"=>" ".number_format($data_piutanglainlain['nilai']),"aktivatetap"=>" ".number_format($data_aktivatetap['nilai']),"akumulasipenyusutan"=>" ".number_format($data_akumulasipenyusutan['nilai']),"hutangkomisi"=>" ".number_format($data_hutangkomisi['nilai']),"biayayangmasihharusdibayar"=>" ".number_format($data_biayayangmasihharusdibayar['nilai']),"hutanglainlain"=>" ".number_format($data_hutanglainlain['nilai']),"modaldisetor"=>" ".number_format($data_modaldisetor['nilai']),"agiodisagiosaham"=>" ".number_format($data_agiodisagiosaham['nilai']),"cadangan"=>" ".number_format($data_cadangan['nilai']),"kenaikanpenurunansuratberharga"=>" ".number_format($data_kenaikanpenurunansuratberharga['nilai']),"selisihpenilaianaktivatetap"=>" ".number_format($data_selisihpenilaianaktivatetap['nilai']),"labarugiditahan"=>" ".number_format($data_labarugiditahan['nilai']),"labarugitahunberjalan"=>" ".number_format($data_labarugitahunberjalan['nilai']),"total"=>" ".number_format($total),"total_aktiva"=>" ".number_format($total_aktiva),"total_kewajiban"=>" ".number_format($total_kewajiban),"total_modalsendiri"=>" ".number_format($total_modalsendiri),"pasiva"=>" ".number_format($pasiva))));
			$datas = (array)$data;
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('cetak_neraca', array(
				'data'=>$datas,
				'id_coa'=>$id_coa,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage();
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_GL.pdf", "I");
		}else{
			echo json_encode(array("piutang_premi"=>"Rp. ".number_format($datas['nilai']),"hutang_premi"=>"Rp. ".number_format($data_hutangpremi['nilai']),"piutangjasakeperantaraan"=>"Rp. ".number_format($data_piutangjasakeperantaraan['nilai']),"kasdanbank"=>"Rp. ".number_format($data_kasdanbank['nilai']),"hutangpajak"=>"Rp. ".number_format($data_hutangpajak['nilai']),"piutangpajak"=>"Rp. ".number_format($data_piutangpajak['nilai']),"investasi"=>"Rp. ".number_format($data_investasi['nilai']),"biayabayardimuka"=>"Rp. ".number_format($data_biayabayardimuka['nilai']),"piutanghasilinvestasi"=>"Rp. ".number_format($data_piutanghasilinvestasi['nilai']),"piutanglainlain"=>"Rp. ".number_format($data_piutanglainlain['nilai']),"aktivatetap"=>"Rp. ".number_format($data_aktivatetap['nilai']),"akumulasipenyusutan"=>"Rp. ".number_format($data_akumulasipenyusutan['nilai']),"hutangkomisi"=>"Rp. ".number_format($data_hutangkomisi['nilai']),"biayayangmasihharusdibayar"=>"Rp. ".number_format($data_biayayangmasihharusdibayar['nilai']),"hutanglainlain"=>"Rp. ".number_format($data_hutanglainlain['nilai']),"modaldisetor"=>"Rp. ".number_format($data_modaldisetor['nilai']),"agiodisagiosaham"=>"Rp. ".number_format($data_agiodisagiosaham['nilai']),"cadangan"=>"Rp. ".number_format($data_cadangan['nilai']),"kenaikanpenurunansuratberharga"=>"Rp. ".number_format($data_kenaikanpenurunansuratberharga['nilai']),"selisihpenilaianaktivatetap"=>"Rp. ".number_format($data_selisihpenilaianaktivatetap['nilai']),"labarugiditahan"=>"Rp. ".number_format($data_labarugiditahan['nilai']),"labarugitahunberjalan"=>"Rp. ".number_format($data_labarugitahunberjalan['nilai']),"total"=>"Rp. ".number_format($total),"total_aktiva"=>"Rp. ".number_format($total_aktiva),"total_kewajiban"=>"Rp. ".number_format($total_kewajiban),"total_modalsendiri"=>"Rp. ".number_format($total_modalsendiri),"pasiva"=>"Rp. ".number_format($pasiva)));
		}
	}
	
	public function actionPolis()
	{

		$this->render('polis',array());
	}
	
	public function actionRugilaba()
	{

		$this->render('rugilaba',array());
	}
	
	public function actionShowrugilaba()
	{
		$sql_piutangjasakeperantaraan = " select @ coalesce(sum(kredit),0) -coalesce(sum(debet),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10P100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P100%'  and a.nominal < 0
  
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal
 + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan

		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment
,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
  
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal
 + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 as kredit,a.nota_debet_no as nota_note,a.note as
 keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
  
union
select 
	0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment
,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as kredit,a.nota_debet_no as nota_note,a.note as keterangan

		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
	  )xx order by nota_note	 
)xx ";
		$cmd_piutangjasakeperantaraan = Yii::app()->db->createCommand($sql_piutangjasakeperantaraan);
		$data_piutangjasakeperantaraan = $cmd_piutangjasakeperantaraan->queryRow();
		
		$sql_pendapatanjasalainnya = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10P200%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P200%'  and a.nominal < 0
  )xx order by nota_note	
)xx ";
		$cmd_pendapatanjasalainnya = Yii::app()->db->createCommand($sql_pendapatanjasalainnya);
		$data_pendapatanjasalainnya = $cmd_pendapatanjasalainnya->queryRow();
		
		$sql_hasilinvestasi = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10P300%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10P300%'  and a.nominal < 0
  )xx order by nota_note	  
)xx ";
		$cmd_hasilinvestasi = Yii::app()->db->createCommand($sql_hasilinvestasi);
		$data_hasilinvestasi = $cmd_hasilinvestasi->queryRow();
		
		$sql_hasillainnya = " select @ coalesce(sum(kredit),0) - coalesce(sum(debet),0) nilai from (
	select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10Z100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z100%'  and a.nominal < 0
  
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no
 as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no
 as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
		0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no
 as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no
 as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
	 )xx order by nota_note	 
)xx ";
		$cmd_hasillainnya = Yii::app()->db->createCommand($sql_hasillainnya);
		$data_hasillainnya = $cmd_hasillainnya->queryRow();
		
		$sql_biayapegawaidanpengurus = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B100%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B100%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayapegawaidanpengurus = Yii::app()->db->createCommand($sql_biayapegawaidanpengurus);
		$data_biayapegawaidanpengurus = $cmd_biayapegawaidanpengurus->queryRow();
		
		$sql_biayapemasaran = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B200%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B200%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayapemasaran = Yii::app()->db->createCommand($sql_biayapemasaran);
		$data_biayapemasaran = $cmd_biayapemasaran->queryRow();
		
		$sql_biayakendaraan = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	 select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B300%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B300%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayakendaraan = Yii::app()->db->createCommand($sql_biayakendaraan);
		$data_biayakendaraan = $cmd_biayakendaraan->queryRow();
		
		$sql_biayaoperasional = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B400%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B400%'  and a.nominal < 0
  )xx order by nota_note	   
)xx ";
		$cmd_biayaoperasional = Yii::app()->db->createCommand($sql_biayaoperasional);
		$data_biayaoperasional = $cmd_biayaoperasional->queryRow();
		
		$sql_biayapenyusutanaktivatetap = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B500%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B500%'  and a.nominal < 0
  )xx order by nota_note	 
)xx ";
		$cmd_biayapenyusutanaktivatetap = Yii::app()->db->createCommand($sql_biayapenyusutanaktivatetap);
		$data_biayapenyusutanaktivatetap = $cmd_biayapenyusutanaktivatetap->queryRow();
		
		$sql_biayaamortisasi = " select coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
	select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 

			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10B600%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10B600%'  and a.nominal < 0
  )xx order by nota_note	
)xx ";
		$cmd_biayaamortisasi = Yii::app()->db->createCommand($sql_biayaamortisasi);
		$data_biayaamortisasi = $cmd_biayaamortisasi->queryRow();
		
		
		$sql_biayanonoperasional = " select @ coalesce(sum(debet),0) - coalesce(sum(kredit),0) nilai from (
		select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code  LIKE '10Z200%' 
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code  LIKE '10Z200%'  and a.nominal < 0
  )xx order by nota_note	 
)xx  ";
		$cmd_biayanonoperasional = Yii::app()->db->createCommand($sql_biayanonoperasional);
		$data_biayanonoperasional = $cmd_biayanonoperasional->queryRow();
		
		
		$totalpendapatanjasa = $data_piutangjasakeperantaraan['nilai'] + $data_pendapatanjasalainnya['nilai'];
		$total_biayaoperasional = $data_biayapegawaidanpengurus['nilai'] + $data_biayapemasaran['nilai'] + $data_biayakendaraan['nilai'] + $data_biayaoperasional['nilai'] + $data_biayapenyusutanaktivatetap['nilai'] + $data_biayaamortisasi['nilai'];
		$total = $totalpendapatanjasa  + $data_hasilinvestasi['nilai'] + $data_hasillainnya['nilai'];
		$total_biaya = $total_biayaoperasional + $data_biayanonoperasional['nilai'];
		$lr_sebelumpajak = $total - $total_biaya;
		$lr_setelahpajak = $total - $total_biaya;
		
		if(isset($_POST['yt0'])){
			$session=new CHttpSession;
            $session->open();
			Yii::import('application.extensions.giiplus.bootstrap.*');
			require_once('tcpdf/tcpdf.php');
			require_once('tcpdf/config/lang/eng.php');
              
			$data = json_decode(json_encode(array("piutangjasakeperantaraan"=>" ".number_format($data_piutangjasakeperantaraan['nilai']),"hasilinvestasi"=>" ".number_format($data_hasilinvestasi['nilai']),"hasillainnya"=>" ".number_format($data_hasillainnya['nilai']),"biayapegawaidanpengurus"=>" ".number_format($data_biayapegawaidanpengurus['nilai']),"biayapemasaran"=>" ".number_format($data_biayapemasaran['nilai']),"biayakendaraan"=>" ".number_format($data_biayakendaraan['nilai']),"biayaoperasional"=>" ".number_format($data_biayaoperasional['nilai']),"biayapenyusutanaktivatetap"=>" ".number_format($data_biayapenyusutanaktivatetap['nilai']),"biayaamortisasi"=>" ".number_format($data_biayaamortisasi['nilai']),"pendapatanjasalainnya"=>" ".number_format($data_pendapatanjasalainnya['nilai']),"total"=>" ".number_format($total),"totalpendapatanjasa"=>" ".number_format($totalpendapatanjasa),"total_biayaoperasional"=>" ".number_format($total_biayaoperasional),"total_biayanonoperasional"=>" ".number_format($data_biayanonoperasional['nilai']),"total_biaya"=>" ".number_format($total_biaya),"lr_sebelumpajak"=>" ".number_format($lr_sebelumpajak),"lr_setelahpajak"=>" ".number_format($lr_setelahpajak))));
			$datas = (array)$data;
			
		$html = $this->renderPartial('cetakrugilaba', array(
				'month'=>$month,
				'tahun'=>$tahun,
				'data'=>$datas,
			), true);
		
		//die($html);
		
			$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage();
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
			$pdf->Output("report_rugilaba.pdf", "I");
		}else{
		
			echo json_encode(array("piutangjasakeperantaraan"=>"Rp. ".number_format($data_piutangjasakeperantaraan['nilai']),"hasilinvestasi"=>"Rp. ".number_format($data_hasilinvestasi['nilai']),"hasillainnya"=>"Rp. ".number_format($data_hasillainnya['nilai']),"biayapegawaidanpengurus"=>"Rp. ".number_format($data_biayapegawaidanpengurus['nilai']),"biayapemasaran"=>"Rp. ".number_format($data_biayapemasaran['nilai']),"biayakendaraan"=>"Rp. ".number_format($data_biayakendaraan['nilai']),"biayaoperasional"=>"Rp. ".number_format($data_biayaoperasional['nilai']),"biayapenyusutanaktivatetap"=>"Rp. ".number_format($data_biayapenyusutanaktivatetap['nilai']),"biayaamortisasi"=>"Rp. ".number_format($data_biayaamortisasi['nilai']),"pendapatanjasalainnya"=>"Rp. ".number_format($data_pendapatanjasalainnya['nilai']),"total"=>"Rp. ".number_format($total),"totalpendapatanjasa"=>"Rp. ".number_format($totalpendapatanjasa),"total_biayaoperasional"=>"Rp. ".number_format($total_biayaoperasional),"total_biayanonoperasional"=>"Rp. ".number_format($data_biayanonoperasional['nilai']),"total_biaya"=>"Rp. ".number_format($total_biaya),"lr_sebelumpajak"=>"Rp. ".number_format($lr_sebelumpajak),"lr_setelahpajak"=>"Rp. ".number_format($lr_setelahpajak)));
		}
	}
	
	
	
	
	
	public function actionJurnal()
	{

		$this->render('jurnal',array());
	}
	
	public function actionBank()
	{

		$this->render('bank',array());
	}
	
	public function actionGeneralledger()
	{

		$this->render('generalledger',array());
	}
	
	public function actionStatistic()
	{

		$this->render('statistic',array());
	}
	
	public function actionRenewal()
	{

		$this->render('renewal',array());
	}
	
	public function actionAging()
	{

		$this->render('aging',array());
	}

	/* Render Report */
	

	public function ConvertBulan($bulan){
		switch ($bulan) {
			case '01':
				$month = 'JANUARI';
				break;
			case '02':
				$month = 'FEBRUARI';
				break;
			case '03':
				$month = 'MARET';
				break;	
			case '04':
				$month = 'APRIL';
				break;
			case '05':
				$month = 'MEI';
				break;
			case '06':
				$month = 'JUNI';
				break;
			case '07':
				$month = 'JULI';
				break;
			case '08':
				$month = 'AGUSTUS';
				break;
			case '09':
				$month = 'SEPTEMBER';
				break;
			case '10':
				$month = 'OKTOBER';
				break;
			case '11':
				$month = 'NOVEMBER';
				break;
			case '12':
				$month = 'DESEMBER';
				break;	
			default:
				break;
		}

		return $month;
	}

	


	


	
	
	
	
	public function actionShowStatistic($t)
	{
	$sql = " 
	select a.name,'HEALTH' as jns_insurance,E.name as jns_mata_uang,sum(c.premium) as gross_premi,sum(brokerage+adjusment-discount) as brokerage
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.name
union 
	select a.name,'NON HEALTH' as jns_insurance,E.name as jns_mata_uang,coalesce(sum(c.premium+c.tpl),0) as gross_premi
	,coalesce(sum(brokerage+adjusment-discount),0) as brokerage
	from mst_client a 
	inner join reg_health_policy_non b on a.id = b.mst_client_id
	inner join ndk_health_non c on c.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.name
  ";
	$command = Yii::app()->db->createCommand($sql);
	$datas = $command->queryAll();
	if($t==1){
		echo json_encode($datas);
			Yii::app()->end();
	}else{
	$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('cetak_statistic', array(
				'data'=>$datas,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_statistic.pdf", "I");
	}
			
	}
	
	
	public function actionExportProduction()
	{
	$sql = " 
	select a.name as nama_client,'HEALTH' as jns_insurance,E.name as currency,sum(c.premium) as nominal,sum(brokerage) as brokerage,
	b.policy_no,to_char(b.start_periode,'dd-mm-yyyy') as start_periode,to_char(b.end_periode,'dd-mm-yyyy') as end_periode
	,c.type
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.name,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy'),c.type
  ";
	
	 $sql2 = " select a.name as nama_client,'NON HEALTH' as jns_insurance,E.name as currency,coalesce(sum(c.premium+c.tpl),0) as nominal
	,coalesce(sum(brokerage),0) as brokerage,b.policy_no
	,to_char(b.start_periode,'dd-mm-yyyy') as start_periode,to_char(b.end_periode,'dd-mm-yyyy') as end_periode,c.type
	from mst_client a 
	inner join reg_health_policy_non b on a.id = b.mst_client_id
	inner join ndk_health_non c on c.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.name,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy'),c.type
 ";
 
	$command = Yii::app()->db->createCommand($sql);
	$datas = $command->queryAll();
	
	$command2 = Yii::app()->db->createCommand($sql2);
	$datas2 = $command2->queryAll();

	$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('cetak_production', array(
				'data'=>$datas,
				'data2'=>$datas2,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_statistic.pdf", "I");
	
			
	}
	
}